
Create PROC [dbo].[STILoadInvoiceForSendToTaxSystem]
    @Startdate VARCHAR(10),
    @EndDate VARCHAR(10),
    @StartTime VARCHAR(10),
    @EndTime VARCHAR(10),
    @TypeFactor TINYINT,
    @ComputerName NVARCHAR(MAX),
    @InvoiceNumber VARCHAR(10)
AS
BEGIN
IF @InvoiceNumber = 0 
  SET @InvoiceNumber=NULL

    
    IF EXISTS
    (
        SELECT *
        FROM [tempdb].dbo.sysobjects
        WHERE name = '##CacheFactor'
              AND xtype = 'U'
    )
    BEGIN
        DELETE FROM [tempdb].dbo.##CacheFactor
        WHERE ComputerName = @ComputerName;
    END;
    ELSE
    BEGIN
        CREATE TABLE ##CacheFactor
        (
            Id BIGINT,
            InvoiceType TINYINT,
            InvoiceNumber VARCHAR(10) COLLATE Arabic_CI_AS,
            SenderName NVARCHAR(35) COLLATE Arabic_CI_AS,
            RecipientName NVARCHAR(35) COLLATE Arabic_CI_AS,
            TotalPrice MONEY,
            FactorDate VARCHAR(10) COLLATE Arabic_CI_AS,
            TimeFactor VARCHAR(5) COLLATE Arabic_CI_AS,
            IsSend BIT DEFAULT (1),
            DeductionFactor MONEY,
            IncrementFactor MONEY,
            ReceiptAndPaymentPrice MONEY,
            liquidationFactor MONEY,
            ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS
        );
    END;


    INSERT INTO ##CacheFactor
    (
        Id,
        InvoiceType,
        InvoiceNumber,
        SenderName,
        RecipientName,
        TotalPrice,
        FactorDate,
        TimeFactor,
        IsSend,
		DeductionFactor,
		IncrementFactor,
		ReceiptAndPaymentPrice,
		liquidationFactor,
        ComputerName
    )
  SELECT theadh.Id_Havaleh,THeadh.Kind_Flag,THeadh.InvoiceNumber,THeadh.Trans_Name,THeadh.Recive_Name,
THeadh.TotalPrice,THeadh.FactorDate,THeadh.TimeFactor,THeadh.IsSend
,TOver_Under.DeductionFactor,TOver_Under.IncrementFactor,ReceiptAndPaymentPrice, (THeadh.TotalPrice + ISNULL(IncrementFactor - DeductionFactor, 0))
           - ISNULL(ReceiptAndPaymentPrice, 0) liquidationFactor,THeadh.ComputerName FROM (
			 SELECT Id_Havaleh,
           Kind_Flag,
           CASE
               WHEN Kind_Flag IN ( 5, 7, 9 ) THEN
                   Nu_Forosh
               WHEN Kind_Flag IN ( 0, 6, 8 ) THEN
                   Nu_Bargasht_Forosh
           END AS InvoiceNumber,
           Trans_Name,
           Recive_Name,
           CASE
               WHEN Kind_Flag IN (  8 ) THEN
                   Tot_Price_KHarid
               WHEN Kind_Flag IN ( 0, 5, 6, 7, 9 ) THEN
                   Tot_Price_forosh
           END AS TotalPrice,
           dbo.MiladiToShamsi( CONVERT(VARCHAR(10), Tarikh_faktor, 111) )AS FactorDate,
           CONVERT(VARCHAR(5), Tarikh, 108)TimeFactor,
           0 AS isSend,
           @ComputerName AS ComputerName
    FROM headH WITH (NOLOCK)
     
    WHERE Kind_Flag = @TypeFactor
          AND CONVERT(VARCHAR(10), Tarikh_faktor, 111)
          BETWEEN (@Startdate) AND (@EndDate)
          AND CONVERT(VARCHAR(10), Tarikh, 108)
          BETWEEN @StartTime AND @EndTime
		

          AND (NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = dbo.headH.Id_Havaleh
    )
              )
) AS THeadh
 LEFT OUTER JOIN 
			  (
			  SELECT headh.Id_Havaleh,
                   SUM(   CASE
                              WHEN Type = 1 THEN
                                  ROUND(Price, 0)
                              ELSE
                                  0
                          END
                      ) IncrementFactor,
                   SUM(   CASE
                              WHEN Type = 0 THEN
                                  ROUND(Price, 0)
                              ELSE
                                  0
                          END
                      ) DeductionFactor
            FROM Over_Under_Factor WITH (NOLOCK)
                INNER JOIN headh
                    ON headh.Id_Havaleh = Over_Under_Factor.Id_Havaleh
            GROUP BY headh.Id_Havaleh

			  )AS TOver_Under
ON TOver_Under.Id_Havaleh = THeadh.Id_Havaleh
LEFT OUTER JOIN 

        (
            SELECT headc.Id_Havaleh,
                   SUM(Ch_Price) ReceiptAndPaymentPrice
            FROM Cheqe WITH (NOLOCK)
                INNER JOIN HeadC WITH (NOLOCK)
                    ON HeadC.Id_Cheqe = Cheqe.Id_Cheqe
                INNER JOIN headh
                    ON headh.Id_Havaleh = HeadC.Id_Havaleh
            GROUP BY headc.Id_Havaleh
        ) AS ReceiptAndPayment
		ON ReceiptAndPayment.Id_Havaleh = THeadh.Id_Havaleh

WHERE InvoiceNumber = ISNULL(@InvoiceNumber, InvoiceNumber)  
END;